﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Entities;
using QLNhaSach;
using System.Windows.Forms;
using System.Data;
using System.Data.SqlClient;

namespace DataAccessLayer
{
    public class SachDAL
    {
        DataService ds = new DataService();
        public SachDAL()
        {
        }
        public Sach GetSachByID(int id)
        {
            Sach s = null;
            try
            {
                string sql = "SELECT * FROM SACH WHERE S_MASACH=" + id;
                SqlDataReader rd = ds.ExcuteDataReader(sql);
                while (rd.Read())
                {
                    s = new Sach();
                    s.MaSach = (int)rd["S_MaSach"];
                    s.TenSach = rd["S_TenSach"].ToString();
                    s.MaTheLoai = (int)rd["S_MaTheLoai"];
                    s.MaTacGia = (int)rd["S_MaTacGia"];
                    s.DonGia = (double)rd["S_DonGia"];
                    s.SoLuongTon = (int)rd["S_SoLuongTon"];
                }
                ds.Close();
            }
            catch (Exception)
            {
                throw;
            }
            return s;

        }
        public DataTable layDanhSachSach()
        {
            DataTable DT = new DataTable();
            try
            {
                string sql = "SELECT * FROM SACH";
                DT = ds.GetTable(sql);
            }
            catch (Exception)
            {
                throw;
            }
            return DT;
        }
        public DataTable TraCuuSach(Sach s, string tenTacGia)
        {
            DataTable dt = null;
            try
            {
                string sql = "SELECT distinct(S_MASACH), S_TENSACH, S_SOLUONGTON, TL_TENTHELOAI,TG_TENTACGIA, S_DONGIA FROM SACH, TACGIA, THELOAI, PHIEUNHAP, CHITIETPHIEUNHAP WHERE  S_MATACGIA = TG_MATACGIA AND S_MATHELOAI = TL_MATHELOAI";
                if (s.MaSach != 0)
                {
                    sql = sql + " AND S_MASACH=" + s.MaSach.ToString();
                }
                if (tenTacGia != "")
                {
                    sql = sql + " AND TG_TENTACGIA LIKE '%" + tenTacGia + "%'";
                }
                if (s.MaTheLoai != -1)
                {
                    sql = sql + " AND S_MATHELOAI=" + s.MaTheLoai;
                }
                if (s.TenSach != "")
                {
                    sql = sql + " AND S_TENSACH LIKE '%" + s.TenSach.ToString() + "%'";
                }
                if (s.DonGia != -1)
                {
                    int GiaSan = 0;
                    int GiaTran = 0;
                    if (s.DonGia == 0)
                    {
                        GiaTran = 100000;
                    }
                    else if (s.DonGia == 1)
                    {
                        GiaSan = 100000;
                        GiaTran = 300000;
                    }
                    else if (s.DonGia == 2)
                    {
                        GiaSan = 300000;
                        GiaTran = 500000;
                    }
                    else if (s.DonGia == 3)
                    {
                        GiaSan = 500000;
                        GiaTran = 1000000;
                    }
                    else if (s.DonGia == 4)
                    {
                        GiaSan = 1000000;
                        GiaTran = 9999999;
                    }
                    sql = sql + " AND S_DONGIA <=" + GiaTran.ToString() + " AND S_DONGIA >=" + GiaSan.ToString();

                }
                
                dt = ds.GetTable(sql);
                ds.Close();
            }
            catch (Exception)
            {
                throw;
            }
            return dt;

        }
        public int InsertSach(Sach s)
        {
            int kq = 0;
            try
            {
                String sql = "INSERT INTO SACH (S_TENSACH,S_MATHELOAI,S_MATACGIA,S_DONGIA,S_SOLUONGTON) VALUES (N'" + s.TenSach + "', " + s.MaTheLoai + "," + s.MaTacGia + "," + s.DonGia + "," + s.SoLuongTon + ")";
                kq = ds.ExcuteInsert(sql);
            }
            catch (Exception)
            {
                throw;
            }
            return kq;

        }
        public int DeleteSach(string maSach)
        {
            int kq = 0;
            try
            {
                String sql = "DELETE FROM SACH WHERE S_MASACH=" + maSach;
                kq = int.Parse(ds.ExcuteInsert(sql).ToString());
            }
            catch (Exception)
            {
                throw;
            }
            return kq;

        }
        public int UpdateSach(Sach s)
        {
            int kq = 0;
            try
            {
                String sql = "UPDATE SACH SET S_TENSACH=N'" +s.TenSach+"', S_MATHELOAI=" + s.MaTheLoai +",S_MATACGIA=" + s.MaTacGia + ",S_DONGIA=" + s.DonGia + ",S_SOLUONGTON=" + s.SoLuongTon + "WHERE S_MASACH=" + s.MaSach;
                kq = int.Parse(ds.ExcuteInsert(sql).ToString());
            }
            catch (Exception)
            {
                throw;
            }
            return kq;

        }
        public int UpdateSoLuongTonCuaSach(Sach s)
        {
            int kq = 0;
            try
            {
                String sql = "UPDATE SACH SET S_SOLUONGTON=" + s.SoLuongTon + "WHERE S_MASACH=" + s.MaSach;
                kq = int.Parse(ds.ExcuteInsert(sql).ToString());
            }
            catch (Exception)
            {
                throw;
            }
            return kq;

        }
    }
}
